﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.CompilerServices;
using DB.SchemaSync.Library.Internal;
using DB.SchemaSync.Library.Models;
using Newtonsoft.Json;

namespace DB.SchemaSync.Library
{
    /// <summary>
    /// 结果对比对象
    /// 典型使用：传入源库 目标库 和 不比较对象集合
    /// 执行 execute（）
    /// SaveScript() 保存差异脚本到文件
    /// GetScriptCommands() 获取差异脚本
    /// </summary>
	public partial class SchemaComparison
	{
		public SchemaComparison(Database source, Database destination, HashSet<ExcludeObject> exclude = null)
		{
			Source = source;
			Destination = destination;
			ExcludeObjects = exclude ?? new HashSet<ExcludeObject>();
		}

		public static SchemaComparison Execute(Database source, Database destination, HashSet<ExcludeObject> exclude = null)
		{
			var sc = new SchemaComparison(source, destination, exclude);
			sc.Execute();
			return sc;
		}

		public void Execute()
		{
			Create = CompareCreateObjects(Source, Destination, ExcludeObjects);
			Alter = CompareAlterObjects(Source, Destination, ExcludeObjects, out List<DbObject> dropRedundant);
			Drop = CompareDropObjects(Source, Destination, ExcludeObjects, dropRedundant);
		}

		public bool AnyDifferences()
		{
			return ((Create?.Any() ?? false) || (Alter?.Any() ?? false) || (Drop?.Any() ?? false));
		}

        /// <summary>
        /// 生成测试用例
        /// </summary>
        /// <returns></returns>
		public TestCase GetTestCase()
		{
			return new TestCase()
			{
				Source = Source,
				Destination = Destination,
				ExcludeObjects = ExcludeObjects,
				Create = Create,
				Alter = Alter,
				Drop = Drop
			};
		}

        /// <summary>
        /// 测试用例保存到文件
        /// </summary>
        /// <param name="fileName"></param>
		public void SaveTestCase(string fileName)
		{
			var testCase = GetTestCase();
			testCase.Save(fileName);
		}

		public Database Source { get; private set; }
		public Database Destination { get; private set; }
		public HashSet<ExcludeObject> ExcludeObjects { get; private set; }

		public IEnumerable<DbObject> Create { get; private set; }
		public IEnumerable<AlteredObject> Alter { get; private set; }
		public IEnumerable<DbObject> Drop { get; private set; }
		public ILookup<string, string> SeedData { get; private set; }


		/// <summary>
		/// 找出需要添加的对象
		/// </summary>
		/// <param name="source"></param>
		/// <param name="destination"></param>
		/// <param name="exclude"></param>
		/// <returns></returns>
		private static IEnumerable<DbObject> CompareCreateObjects(Database source, Database destination, IEnumerable<ExcludeObject> exclude)
		{
			List<DbObject> results = new List<DbObject>();

			var newSchemas = source.Schemas.Where(s => !destination.Schemas.Contains(s));
			results.AddRange(newSchemas);

			var newTables = source.Tables.Where(t => !destination.Tables.Contains(t));
			results.AddRange(newTables);

			//已匹配的表
			var matchingTables = from s in source.Tables
								 join d in destination.Tables on s equals d
								 select new { SourceTable = s, DestRowCount = d.RowCount, DestTable = d };

			//已匹配 表字典 key 表， value 表列数
			Dictionary<Table, int> destRowCounts = matchingTables.ToDictionary(row => row.SourceTable, row => row.DestRowCount);

			var newColumns = matchingTables
				.SelectMany(t => t.SourceTable.Columns)
				.Where(c => !destination.Tables.SelectMany(t => t.Columns).Contains(c));

			// for proper detection of empty tables in dest database
            foreach (var col in newColumns)
            {
                col.Table.RowCount = destRowCounts[col.Table];
            }
			results.AddRange(newColumns);

			// must include only "safe" tables (i.e. any that are not rebuilt -- such as those with data nor any modified columns)
			// because rebuilt tables get their own index creations
			// and their indexes are automatically included in the rebuild
			// 新增的表必然会建索引，这里只挑选出已匹配表 索引的差异
			var newIndexes = matchingTables
				.Where(t => destRowCounts[t.SourceTable] > 0 || !HasModifiedColumns(t.SourceTable, t.DestTable))
				.SelectMany(t => t.SourceTable.Indexes)
				.Where(x => !destination.Tables.SelectMany(t => t.Indexes).Contains(x));
			results.AddRange(newIndexes);

			var newForeignKeys = source.ForeignKeys.Where(fk => !destination.ForeignKeys.Contains(fk));
			results.AddRange(newForeignKeys);

			var newModules = source.Modules?.Where(m => !destination.Modules.Contains(m));
			if (newModules != null) results.AddRange(newModules);

			return results.Where(obj => !exclude.Contains(obj.GetExcludeObject())).ToArray();
		}

		/// <summary>
		/// 对比两张表有不同列
		/// </summary>
		/// <param name="sourceTable"></param>
		/// <param name="destTable"></param>
		/// <returns></returns>
		private static bool HasModifiedColumns(Table sourceTable, Table destTable)
		{
			var alteredColumns = from src in sourceTable.Columns
								 join dest in destTable.Columns on src equals dest
								 where src.IsAltered(dest)
								 select src;
			if (alteredColumns.Any()) return true;

			var newColumns = sourceTable.Columns.Where(col => !destTable.Columns.Contains(col));
			if (newColumns.Any()) return true;

			var droppedColumns = destTable.Columns.Where(col => !sourceTable.Columns.Contains(col));
			if (droppedColumns.Any()) return true;

			return false;
		}


		/// <summary>
		/// 找出修改的对象
		/// </summary>
		/// <param name="source"></param>
		/// <param name="destination"></param>
		/// <param name="exclude"></param>
		/// <param name="dropRedundant"></param>
		/// <returns></returns>
		private static IEnumerable<AlteredObject> CompareAlterObjects(Database source, Database destination, IEnumerable<ExcludeObject> exclude, out List<DbObject> dropRedundant)
		{
			List<AlteredObject> results = new List<AlteredObject>();

			var alteredColumns = from s in source.Tables.SelectMany(t => t.Columns)
								 join d in destination.Tables.SelectMany(t => t.Columns) on s equals d
								 where s.IsAltered(d)
								 select new AlteredObject() { Source = s, Destination = d };
			results.AddRange(alteredColumns);

			var alteredKFs = from s in source.ForeignKeys
							 join d in destination.ForeignKeys on s equals d
							 where s.IsAltered(d)
							 select new AlteredObject() { Source = s, Destination = d };
			results.AddRange(alteredKFs);

			var alteredIndexes = (from s in source.Tables.SelectMany(t => t.Indexes)
								  join d in destination.Tables.SelectMany(t => t.Indexes) on s equals d
								  where s.IsAltered(d)
								  select new AlteredObject() { Source = s, Destination = d }).ToArray();
			results.AddRange(alteredIndexes);

			// this happens when you remove a PK, leaving you with a newly created identity PK that overlaps with the existing unique constraint
			// the original unique constraint needs to be deleted because the indexes are redundant,
			// but don't include indexes that you are explicitly altering
			var overlappingIndexes = (from s in source.Tables.SelectMany(t => t.Indexes)
									  join d in destination.Tables.SelectMany(t => t.Indexes) on new IndexColumnComparer(s.Table, s.Columns) equals new IndexColumnComparer(d.Table, d.Columns)
									  where !s.Name.ToLower().Equals(d.Name.ToLower()) && !alteredIndexes.Any(altered => altered.Source.Equals(d))
									  select d).ToArray();
			dropRedundant = new List<DbObject>();
			dropRedundant.AddRange(overlappingIndexes);

			if (source.Modules != null && destination.Modules != null)
			{
				var alteredModules = from s in source.Modules
									 join d in destination.Modules on s equals d
									 where s.IsAltered(d)
									 select new AlteredObject() { Source = s, Destination = d };
				results.AddRange(alteredModules);
			}

			return results.Where(altered => !exclude.Contains(altered.Source.GetExcludeObject())).ToArray();
		}


		/// <summary>
		/// 找出删除对象
		/// </summary>
		/// <param name="source"></param>
		/// <param name="destination"></param>
		/// <param name="exclude"></param>
		/// <param name="dropRedundant"></param>
		/// <returns></returns>
		private IEnumerable<DbObject> CompareDropObjects(Database source, Database destination, IEnumerable<ExcludeObject> exclude, IEnumerable<DbObject> dropRedundant)
		{
			List<DbObject> results = new List<DbObject>();

			if (dropRedundant.Any()) results.AddRange(dropRedundant);

			var droppedSchemas = destination.Schemas.Where(s => !source.Schemas.Contains(s));
			results.AddRange(droppedSchemas);

			var droppedTables = destination.Tables.Where(t => !source.Tables.Contains(t));
			results.AddRange(droppedTables);

			var destMatchingTables = from s in source.Tables
									 join d in destination.Tables on s equals d
									 select d;

			var srcColumns = source.Tables.SelectMany(t => t.Columns).ToArray();
			var destColumns = destination.Tables.SelectMany(t => t.Columns).ToArray();
			var droppedColumns = destColumns.Where(col => !srcColumns.Contains(col) && !droppedTables.Contains(col.Table)).ToArray();
			var columnDroppedFKs = destination.ForeignKeys.Where(fk => FKContainsDroppedColumn(fk, droppedColumns));
			results.AddRange(droppedColumns);

			var destIndexes = destMatchingTables.SelectMany(t => t.Indexes);
			var srcIndexes = source.Tables.SelectMany(t => t.Indexes);			
			var droppedIndexes = destIndexes.Where(x => !srcIndexes.Contains(x));
			//DebugWrite("droppedIndexes.json", new { tables = destMatchingTables.Select(t => t.Name), dest = destIndexes.Select(x => x.Name), src = srcIndexes.Select(x => x.Name), dropped = droppedIndexes });
			results.AddRange(droppedIndexes.Where(x => !results.Exists( y => y.ObjectId == x.ObjectId)));

			var droppedFKs = destination.ForeignKeys.Where(fk =>
				!source.ForeignKeys.Contains(fk) &&
				!droppedTables.Contains(fk.ReferencingTable) &&
				!columnDroppedFKs.Contains(fk));
			results.AddRange(droppedFKs);

			var droppedModules = destination.Modules?.Where(m => !source.Modules?.Contains(m) ?? false);
			results.AddRange(droppedModules);

			return results.Where(obj => !exclude.Contains(obj.GetExcludeObject())).ToArray();
		}

		private bool FKContainsDroppedColumn(ForeignKey fk, Column[] droppedColumns)
		{
			return fk.Columns.Any(fkcol => droppedColumns.Any(col => col.Name.Equals(fkcol.ReferencingName) && fk.ReferencingTable.Equals(col.Table)));
		}

        /// <summary>
        /// 产生脚本块
        /// </summary>
        /// <param name="syntax">sql方言</param>
        /// <returns></returns>
		public IEnumerable<ScriptBlock> GetScriptBlocks(SqlSyntax syntax,bool checkSelected = false)
		{
            //遍历新增对
			var createColumns = Create.OfType<Column>();
            var createModule = Create.OfType<Module>();
			var createOther = Create.Where(obj => !obj.GetType().Equals(typeof(Column)) && !obj.GetType().Equals(typeof(Module)));
            

			List<ScriptBlock> fkScript = new List<ScriptBlock>();
			List<ForeignKey> createFKs = new List<ForeignKey>();
			List<ForeignKey> alreadyDroppedFKs = new List<ForeignKey>();


            //遍历删除对象
            foreach (var drop in Drop)
            {
                var tbl = drop as Table;
                if (tbl != null)
                {
                    continue; //删除表不执行，防止数据误删除
                    AddReferencingFKs(Destination, tbl, alreadyDroppedFKs);
                }

                var fk = drop as ForeignKey;
                if (fk != null && alreadyDroppedFKs.Contains(fk)) continue;

                yield return new ScriptBlock()
                {
                    ActionType = ActionType.Drop,
                    Objects = new DbObject[] { drop },
                    Commands = DropCommands(syntax, drop)
                };
            }

			foreach (var columnGrp in createColumns.GroupBy(item => item.Table))
			{
				string columnList = string.Join(", ", columnGrp.Select(col => col.Name));

				if (columnGrp.Key.IsEmpty)
				{
					yield return new ScriptBlock()
					{
						ActionType = ActionType.Rebuild,
						Objects = columnGrp,
						Commands = RebuildCommands(syntax, columnGrp, columnList)
					};

					foreach (var fk in columnGrp.Key.GetForeignKeys(Source))
					{
						fkScript.Add(new ScriptBlock()
						{
							ActionType = ActionType.Rebuild,
							Objects = columnGrp,
							Commands = CreateCommands(syntax, fk)
						});
						createFKs.Add(fk);
					}
				}
				else
				{
					foreach (var col in columnGrp)
					{
						yield return new ScriptBlock()
						{
							ActionType = ActionType.Create,
							Objects = new DbObject[] { col },
							Commands = CreateCommands(syntax, col)
						};

						if (col.IsForeignKey(Source, out ForeignKey fk))
						{
							fkScript.Add(new ScriptBlock()
							{
								ActionType = ActionType.Create,
								Objects = new DbObject[] { col },
								Commands = CreateCommands(syntax, fk)
							});
						}
					}
				}
			}


            //修改对象
            foreach (var alter in Alter)
            {
                //存储过程函数先掠过
                if (alter != null && alter.Source.GetType().Equals(typeof(Module)))
                {
                    continue;
                }

                yield return new ScriptBlock()
                {
                    ActionType = ActionType.Alter,
                    Objects = new DbObject[] { alter.Source },
                    DestinationObjects = new DbObject[] { alter.Destination },
                    Commands = AlterCommands(syntax, alter.Source),
                    DestinationCommands = AlterCommands(syntax, alter.Destination)
                };
            }


            foreach (var create in createOther)
            {
                var fk = create as ForeignKey;
                if (fk != null && createFKs.Contains(fk)) continue;

                yield return new ScriptBlock()
                {
                    ActionType = ActionType.Create,
                    Objects = new DbObject[] { create },
                    Commands = CreateCommands(syntax, create)
                };
            }


		


            //创建存储过程函数
            foreach (var create in createModule)
            {
                yield return new ScriptBlock()
                {
                    ActionType = ActionType.Create,
                    Objects = new DbObject[] { create },
                    Commands = CreateCommands(syntax, create)
                };
            }

            //修改存储过程函数
            foreach (var alter in Alter)
            {
                //存储过程函数先掠过
                if (alter != null && alter.Source.GetType().Equals(typeof(Module)))
                {
                    yield return new ScriptBlock()
                    {
                        ActionType = ActionType.Alter,
                        Objects = new DbObject[] { alter.Source },
                        DestinationObjects = new DbObject[] { alter.Destination },
                        Commands = AlterCommands(syntax, alter.Source),
                        DestinationCommands = AlterCommands(syntax, alter.Destination)
                    };
                }
            }

            //遍历外键
            foreach (var fk in fkScript) yield return fk;
		}

		private void AddReferencingFKs(Database db, Table tbl, List<ForeignKey> dropFKs)
		{
			var deps = tbl.GetDependencies(db);
			dropFKs.AddRange(deps.OfType<ForeignKey>());
		}

        /// <summary>
        /// 删除命令
        /// </summary>
        /// <param name="syntax"></param>
        /// <param name="drop"></param>
        /// <returns></returns>
		private IEnumerable<string> DropCommands(SqlSyntax syntax, DbObject drop)
		{
			var deps = drop.GetDependencies(Destination);
			foreach (var obj in deps)
			{
				foreach (var cmd in obj.DropCommands(syntax)) yield return syntax.ApplyDelimiters(cmd);
			}

			foreach (var cmd in drop.DropCommands(syntax)) yield return syntax.ApplyDelimiters(cmd);
		}

        /// <summary>
        /// 变更命令
        /// </summary>
        /// <param name="syntax"></param>
        /// <param name="alter"></param>
        /// <returns></returns>
		private IEnumerable<string> AlterCommands(SqlSyntax syntax, DbObject alter)
		{
			foreach (var cmd in alter.AlterCommands(syntax, Source)) yield return syntax.ApplyDelimiters(cmd);
		}

        /// <summary>
        /// 创建命令
        /// </summary>
        /// <param name="syntax"></param>
        /// <param name="object"></param>
        /// <returns></returns>
		private IEnumerable<string> CreateCommands(SqlSyntax syntax, DbObject @object)
		{
			//yield return $"{syntax.CommentStart} table {tbl.Key} has {tbl.Key.RowCount:n0} rows, so new columns are added individually";
			foreach (var cmd in @object.CreateCommands(syntax)) yield return syntax.ApplyDelimiters(cmd);
		}

        /// <summary>
        /// 重建命令
        /// </summary>
        /// <param name="syntax"></param>
        /// <param name="tblGrp"></param>
        /// <param name="columnList"></param>
        /// <returns></returns>
		private IEnumerable<string> RebuildCommands(SqlSyntax syntax, IGrouping<Table, Column> tblGrp, string columnList)
		{
			yield return $"{syntax.CommentStart} rebuilding empty table {tblGrp.Key} to add column(s) {columnList}";
			
			/*
			var dependencies = tblGrp.Key.GetDependencies(Source).ToArray();
			var newTables = Create.OfType<Table>().ToArray();			
			var newFKs = dependencies.OfType<ForeignKey>().Where(fk => newTables.Contains(fk.ReferencingTable)).ToArray();
			var filteredDeps = dependencies.Except(newFKs).ToArray();
			*/

			foreach (var cmd in tblGrp.Key.AlterCommands(syntax, Source)) yield return syntax.ApplyDelimiters(cmd);
		}

		/*
		workaround for bad debugger behavior in visual studio (I was getting "internal compiler error" trying to view
		various objects in break mode, so I wrote them to json instead)
		private void SaveData(object @object)
		{
			using (var writer = File.CreateText(@"c:\users\adam\desktop\output.json"))
			{			
				string json = JsonConvert.SerializeObject(@object, new JsonSerializerSettings()
				{
					Formatting = Formatting.Indented,
					ReferenceLoopHandling = ReferenceLoopHandling.Ignore
				});
				writer.Write(json);
			}
		}
		*/

        /// <summary>
        /// 输出脚本命令
        /// </summary>
        /// <param name="syntax"></param>
        /// <returns></returns>
		public IEnumerable<string> GetScriptCommands(SqlSyntax syntax)
		{
			//先产生脚本块
			//return GetScriptBlocks(syntax).OrderBy(b => b.ActionType).SelectMany(block => block.GetCommands(syntax));
            return GetScriptBlocks(syntax).SelectMany(block => block.GetCommands(syntax));
		}

		/// <summary>
		/// 根据选择输出SQL
		/// </summary>
		/// <param name="syntax"></param>
		/// <returns></returns>
        public IEnumerable<string> GetScriptCommandsCheckSelected(SqlSyntax syntax)
        {
			//先产生脚本块
			//return GetScriptBlocks(syntax).OrderBy(b => b.ActionType).SelectMany(block => block.GetCommandsCheckSelected(syntax));
			return GetScriptBlocks(syntax).SelectMany(block => block.GetCommandsCheckSelected(syntax));
		}

		/// <summary>
		/// 保存脚本命令
		/// </summary>
		/// <param name="syntax"></param>
		/// <param name="path"></param>
		public void SaveScript(SqlSyntax syntax, string path)
		{
			using (var file = File.CreateText(path))
			{
				foreach (var cmd in GetScriptCommands(syntax))
				{
					file.WriteLine(cmd);
				}
			}
		}

		/// <summary>
		/// This is because Visual Studio is giving "internal compiler error" when I try to view debug variables
		/// It's possibly this issue: https://github.com/dotnet/roslyn/issues/32249
		/// see also https://stackoverflow.com/questions/50039542/vs-2017-immediate-window-shows-internal-error-in-the-c-sharp-compiler
		/// </summary>
		private void DebugWrite(string fileName, object data)
		{
			string outputFile = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "SchemaSync", fileName);
			if (File.Exists(outputFile)) File.Delete(outputFile);

			string folder = Path.GetDirectoryName(outputFile);
			if (!Directory.Exists(folder)) Directory.CreateDirectory(folder);

			using (var file = File.CreateText(outputFile))
			{
				string json = JsonConvert.SerializeObject(data, new JsonSerializerSettings()
				{
					Formatting = Formatting.Indented,
					ReferenceLoopHandling = ReferenceLoopHandling.Ignore
				});
				file.Write(json);
			}
		}
	}
}